﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;

namespace NLBS.Data
{
    public partial class SqlDataProvider : DbProvider
    {
        #region trackback

        public override IList<TrackBack> GetTrackBack()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT tTB.*, tLog.log_authorID, tLog.log_title,tLog.log_catID
            FROM [blog_Article] tLog, [blog_Trackback] tTB WHERE tLog.log_id=tTB.log_id ORDER BY tTB.tb_time DESC");

            IList<TrackBack> lists = new List<TrackBack>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    TrackBack tb = CommonDataProvider.PopulateTrackBackFromIDataReader(dataReader);
                    tb.Article.AuthorID = Convert.ToInt32(dataReader["log_authorID"]);
                    tb.Article.Title = Convert.ToString(dataReader["log_title"]);
                    tb.Article.CategoryID = Convert.ToInt32(dataReader["log_catID"]);
                    lists.Add(tb);
                }
            }
            return lists;
        }

        public override IPagedCollection<TrackBack> GetTrackBackBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });

            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("tb_title LIKE '%{0}%'", prefixArray[i]);
            }
            
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT top {0} tb.*, 
            tLog.log_authorID, tLog.log_title,tLog.log_catID
            FROM [blog_Article] tLog, [TrackBack] tb
            WHERE  tLog.log_id=tTB.log_id and tb_id<=(select min(tb_id) 
            from (select top {1} tb_id from TrackBack order by tb_id desc) as T) AND {2} 
            ORDER BY tb_time DESC;
            SELECT COUNT(tb_id) AS TotalRecords from TrackBack where {2}", startIndex, pageSize, sb.ToString()));
            IDataReader dr = db.ExecuteReader(dbCommand);
            IPagedCollection<TrackBack> lists = new PagedCollection<TrackBack>();
            TrackBack tb = null;
            while (dr.Read())
            {
                tb = CommonDataProvider.PopulateTrackBackFromIDataReader(dr);
                tb.Article.AuthorID = Convert.ToInt32(dr["log_authorID"]);
                tb.Article.Title = Convert.ToString(dr["log_title"]);
                tb.Article.CategoryID = Convert.ToInt32(dr["log_catID"]);
                lists.Add(tb);
            }
            lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dr);
            return lists;
        }

        public override void InsertTrackBack(TrackBack tb)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("INSERT INTO [dbo].[blog_Trackback] ([log_id], [tb_url], [tb_title], [tb_blog], [tb_excerpt], [tb_time], [tb_ip]) VALUES (@log_id, @tb_url, @tb_title, @tb_blog, @tb_excerpt, @tb_time, @tb_ip)");
            db.AddInParameter(dbCommand, "log_id", DbType.Int32, tb.LogID);
            db.AddInParameter(dbCommand, "tb_url", DbType.String, tb.Url);
            db.AddInParameter(dbCommand, "tb_title", DbType.String, tb.Title);
            db.AddInParameter(dbCommand, "tb_blog", DbType.String, tb.BlogName);
            db.AddInParameter(dbCommand, "tb_excerpt", DbType.String, tb.Excerpt);
            db.AddInParameter(dbCommand, "tb_time", DbType.DateTime, tb.Time);
            db.AddInParameter(dbCommand, "tb_ip", DbType.String, tb.IP);
            db.ExecuteNonQuery(dbCommand);
        }

        public override TrackBack GetTrackBackByID(int id)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("select * from blog_Trackback where tb_id=@tbID");
            db.AddInParameter(dbCommand, "tbID", DbType.Int32, id);
            TrackBack tb = null;
            IDataReader dr = db.ExecuteReader(dbCommand);
            if (dr.Read())
            {
                tb = CommonDataProvider.PopulateTrackBackFromIDataReader(dr);
            }
            return tb;
        }

        public override TrackBack GetTrackBackByName(string title, string excerpt)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("select * from blog_Trackback where tb_title=@title and tb_excerpt=@excerpt");
            db.AddInParameter(dbCommand, "title", DbType.String, title);
            db.AddInParameter(dbCommand, "excerpt", DbType.String, excerpt);
            TrackBack tb = null;
            IDataReader dr = db.ExecuteReader(dbCommand);
            if (dr.Read())
            {
                tb = CommonDataProvider.PopulateTrackBackFromIDataReader(dr);
            }
            return tb;
        }

        public override void UpdateTrackBack(TrackBack tb)
        {

        }

        public override void DeleteTrackBack(int id)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("delete blog_trackback where tb_id=@trackbackID");
            db.AddInParameter(dbCommand, "trackbackID", DbType.Int32, id);
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
